package com.ejoysoft.wlanyz.business;


import java.io.File;
import java.sql.ResultSet;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Format;
import com.ejoysoft.common.Globa;
import com.ejoysoft.common.UID;



public class Comunity {
	private Globa globa;
    private DbConnect db;
    private DbConnect dd = new DbConnect();

    public Comunity() {
    }

    public Comunity(Globa globa) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
    }

    public Comunity(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName = "WLAN_RESC_COMUNITY";

    
  //增加
    public boolean add(String strCommId) {
        String strSql = "";
        strCommId = UID.getID();
        try {
        	//把用户信息写入数据库
            strSql = "INSERT INTO " + strTableName + "  (strCommId,strCity,strTown,strSiteName,strSiteNum," +
            		"strSiteType,strLongitude,strLatitude,strLac,strCI,strSiteAttitude," +
            		"strSiteAddr,strSiteRemark,strCreator,dtCreatDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
            db.prepareStatement(strSql);
            
            db.setString(1, strCommId);
            db.setString(2, strCity);
            db.setString(3, strTown);  
            db.setString(4, strSiteName);
            db.setString(5, strSiteNum);
            db.setString(6, strSiteType);
            db.setString(7, strLongitude);
            db.setString(8, strLatitude);
            db.setString(9, strLac);
            db.setString(10, strCI);
            db.setString(11, strSiteAttitude);               
            db.setString(12, strSiteAddr);
            db.setString(13, strSiteRemark);
            db.setString(14, strCreator);
            db.setString(15, Format.getDateTime());
           
            if (db.executeUpdate() > 0) {
                Globa.logger0("增加用户信息", globa.loginName, globa.loginIp, strSql, "用户管理", globa.userSession.getStrDepart());
                System.out.println("!!!!!!!!!!!!!!");
                return true;
            } else
                return false;
        } catch (Exception e) {
            System.out.println("增加用户信息时出错！！");
            e.printStackTrace();
            return false;
        }
    }
   //删除
    public boolean delete(String where,String strCommId) {
        try {
        	
        	
            String sql = "DELETE FROM " + strTableName + "  ".concat(where);
            System.out.println(sql);
            db.executeUpdate(sql);         
            return true;
        } catch (Exception ee) {
            ee.printStackTrace();
            return false;
        }
    }
    //修改
    public boolean update(String strCommId) {
        try {
            String strSql = "UPDATE  " + strTableName + "  SET strCity=?,strTown=?,strSiteName=?,strSiteNum=?," +
            		"strSiteType=?,strLongitude=?,strLatitude=?,strLac=?,strCI=?,strSiteAttitude=?," +
            		"strSiteAddr=?,strSiteRemark=?,strCreator=?, dtCreatDate= to_date(?,'yyyy-MM-dd hh24:mi:ss') WHERE strCommId=? ";
            db.prepareStatement(strSql);
            
            db.setString(1, strCity);
            db.setString(2, strTown);  
            db.setString(3, strSiteName);
            db.setString(4, strSiteNum);
            db.setString(5, strSiteType);
            db.setString(6, strLongitude);
            db.setString(7, strLatitude);
            db.setString(8, strLac);
            db.setString(9, strCI);
            db.setString(10, strSiteAttitude);               
            db.setString(11, strSiteAddr);
            db.setString(12, strSiteRemark);
            db.setString(13, strCreator);
            db.setString(14, Format.getDateTime());
            db.setString(15, strCommId);
            db.executeUpdate();
            Globa.logger0("修改用户信息", globa.loginName, globa.loginIp, strSql, "用户管理", globa.userSession.getStrDepart());
            return true;
        } catch (Exception e) {
            System.out.println("修改用户信息时出错：" + e);
            e.printStackTrace();
            return false;
        }
    }

//返回单条记录
    public Comunity show(String where) {
        try {
            String strSql = "select * from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }

  //查询符合条件的记录总数
    public int getCount(String where) {
        int count = 0;
        try {
            String sql = "SELECT count(strCommId) FROM " + strTableName + "  ";
            if (where.length() > 0) {
                where = where.toLowerCase();
                if (where.indexOf("order") > 0)
                    where = where.substring(0, where.lastIndexOf("order"));
                sql = String.valueOf(sql) + String.valueOf(where);
            }
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            return count;
        }
    }



    //记录集转化为对象
    public Comunity load(ResultSet rs, boolean isView) {
    	Comunity theBean = new Comunity();
        
        try {
        	
        	
        	theBean.setStrCommId(rs.getString(1));
            theBean.setStrCity(rs.getString(2));
            theBean.setStrTown(rs.getString(3));
            theBean.setStrSiteName(rs.getString(4));
            theBean.setStrSiteNum(rs.getString(5));
            theBean.setStrSiteType(rs.getString(6));
            theBean.setStrLongitude(rs.getString(7));
            theBean.setStrLatitude(rs.getString(8));
            theBean.setStrLac(rs.getString(9));
            theBean.setStrCI(rs.getString(10));
            theBean.setStrSiteAttitude(rs.getString(11));
            theBean.setStrSiteAddr(rs.getString(12));
            theBean.setStrSiteRemark(rs.getString(13));
            theBean.setStrCreator(rs.getString(14));
            theBean.setDtCreatDate(rs.getString(15));

        } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }

 
	//列表记录集
    public Vector<Comunity> list(String where, int startRow, int rowCount) {
        Vector<Comunity> beans = new Vector<Comunity>();
        try {
            String sql = "SELECT *  FROM  " + strTableName + " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            System.out.println(db+"====================");
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            System.out.println("!!!!!!!"+sql);
            ResultSet rs = s.executeQuery(sql);
            System.out.println("!!!!!!!");
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                	Comunity theBean = new Comunity();
                    theBean = load(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
        
    }
    
    //列表记录集
    public Vector<String> listDeviceId(String where) {
        Vector<String>  beans = new Vector<String>();
        try {
          String sql = "SELECT strCommId from  "+ strTableName+ " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            ResultSet rs = db.executeQuery(sql);
            if (rs != null && rs.next()) {
               do { 
                    beans.add(rs.getString("strCommId"));
                } while (rs.next());
            }
            rs.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        db.closeCon();
        return beans;
        
    }

  //********************导入**************************************
    public void insertinto(String s1,String s2,String s3,String s4,String s5,String s6,String s7,String s8,String s9,String s10,String s11,String s12,String s13,String s14){
    	String strSql = "";
		try {
			String strCommId = UID.getID();
	        strSql = "INSERT INTO WLAN_RESC_COMUNITY (strCommId, strCity, strTown, strSiteName, strSiteNum, strSiteType," +
	        		" strLongitude, strLatitude, strLac, strCI, strSiteAttitude, strSiteAddr, strSiteRemark, strCreator, dtCreatDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
	        //strSql = "INSERT INTO RM_DEVICE (device_Id, device_Name, device_Alias, strMac_Addr, strHotSpot)VALUES (?,?,?,?,?)";
	        //System.out.println("huoqushijian:"+Format.getDateTime());
	        dd.prepareStatement(strSql);
	        dd.setString(1, strCommId);
	        dd.setString(2, s1);
	        dd.setString(3, s2);
	        dd.setString(4, s3);
	        dd.setString(5, s4);
	        dd.setString(6, s5);
	        dd.setString(7, s6);
	        dd.setString(8, s7);
	        dd.setString(9, s8);
	        dd.setString(10, s9);
	        dd.setString(11, s10);
	        dd.setString(12, s11);
	        dd.setString(13, s12);
	        dd.setString(14, s13);
	        dd.setString(15, Format.getDateTime());
			
	        if (dd.executeUpdate() > 0) {
	        	//Globa.logger0("导入excel表格成功", globa.loginName, globa.loginIp, strSql, "资源管理", globa.userSession.getStrDepart());
	        	System.out.println("导入成功");
	        } 
		} catch (Exception e) {
			//dd.rollback();
	        e.printStackTrace();
	        //dd.closeCon();
		}
		dd.closeCon();
    }
    public void method(String url){
    	try
	    {
	    List<String> list = new ArrayList<String>();
	    Workbook workBook=Workbook.getWorkbook(new File(url));
	    //获得第一个工作表对象
	    Sheet sheet=workBook.getSheet(0);
	    
	    //得到第一列第一行的单元格
	    Cell cell=null;
	
	    
	    for (int i = 1; i < sheet.getRows(); i++){
	        List<String> list2 = new ArrayList<String>();
	        for (int j = 0; j < sheet.getColumns(); j++) {
	        	cell = sheet.getCell(j, i);
	        	list2.add(cell.getContents().toString());//将每行的字符串用一个String类型的集合保存。
	        }
	        //System.out.println("输出list的大小："+list2.size());
	        //System.out.println("输出一行："+list2.get(18)+","+list2.get(19)+","+list2.get(17));
	        insertinto(list2.get(0),list2.get(1),list2.get(2),list2.get(3),list2.get(4),list2.get(5),list2.get(6),list2.get(7),list2.get(8),list2.get(9),list2.get(10),list2.get(11),list2.get(12),list2.get(13));
	    }
	    	workBook.close();//记得关闭
	      
	      //迭代集合查看每行的数据
		    
		    
	    }catch(Exception e)
	    {
	    System.out.println(e);
	    }
    }
   
    
    private String strCommId;//小区号
    private String strCity;//地市
    private String strTown;//县区
    private String strSiteName;//站名
    private String strSiteNum;//站号
    private String strSiteType;//站型
    private String strLongitude;//经度
    private String strLatitude;//纬度
    private String strLac;//位置区码
    private String strCI;//小区识别
    private String strSiteAttitude;//站址状态
    private String strSiteAddr;//所在地
    private String strSiteRemark;//备注
    private String strCreator;//操作人
    private String dtCreatDate;//操作时间


	

	public String getStrCommId() {
		return strCommId;
	}

	public void setStrCommId(String strCommId) {
		this.strCommId = strCommId;
	}

	public String getStrCity() {
		return strCity;
	}

	public void setStrCity(String strCity) {
		this.strCity = strCity;
	}

	public String getStrTown() {
		return strTown;
	}

	public void setStrTown(String strTown) {
		this.strTown = strTown;
	}

	public String getStrSiteName() {
		return strSiteName;
	}

	public void setStrSiteName(String strSiteName) {
		this.strSiteName = strSiteName;
	}

	public String getStrSiteNum() {
		return strSiteNum;
	}

	public void setStrSiteNum(String strSiteNum) {
		this.strSiteNum = strSiteNum;
	}

	public String getStrSiteType() {
		return strSiteType;
	}

	public void setStrSiteType(String strSiteType) {
		this.strSiteType = strSiteType;
	}

	public String getStrLongitude() {
		return strLongitude;
	}

	public void setStrLongitude(String strLongitude) {
		this.strLongitude = strLongitude;
	}

	public String getStrLatitude() {
		return strLatitude;
	}

	public void setStrLatitude(String strLatitude) {
		this.strLatitude = strLatitude;
	}

	public String getStrLac() {
		return strLac;
	}

	public void setStrLac(String strLac) {
		this.strLac = strLac;
	}

	public String getStrCI() {
		return strCI;
	}

	public void setStrCI(String strCI) {
		this.strCI = strCI;
	}

	public String getStrSiteAttitude() {
		return strSiteAttitude;
	}

	public void setStrSiteAttitude(String strSiteAttitude) {
		this.strSiteAttitude = strSiteAttitude;
	}

	public String getStrSiteAddr() {
		return strSiteAddr;
	}

	public void setStrSiteAddr(String strSiteAddr) {
		this.strSiteAddr = strSiteAddr;
	}

	public String getStrSiteRemark() {
		return strSiteRemark;
	}

	public void setStrSiteRemark(String strSiteRemark) {
		this.strSiteRemark = strSiteRemark;
	}


	public String getStrCreator() {
		return strCreator;
	}

	public void setStrCreator(String strCreator) {
		this.strCreator = strCreator;
	}

	public String getDtCreatDate() {
		return dtCreatDate;
	}

	public void setDtCreatDate(String dtCreatDate) {
		this.dtCreatDate = dtCreatDate;
	}


}